<?php
class TgdongdoanphikhoasController extends AppController {

	var $name = 'Tgdongdoanphikhoas';
	var $uses = array('Tgdongdoanphikhoa','Khoa','Thang','Chidoansv','Doanphisv','Doanphicb','Namhoc','Khoahoc');
	var $helpers=array('ctable');
	function index() {
		$this->Tgdongdoanphikhoa->recursive = 0;
		$this->set('tgdongdoanphikhoas', $this->paginate());
	}

	function view($id = null) {
		if (!$id) {
			$this->Session->setFlash(__('Invalid tgdongdoanphikhoa', true));
			$this->redirect(array('action' => 'index'));
		}
		$this->set('tgdongdoanphikhoa', $this->Tgdongdoanphikhoa->read(null, $id));
	}

	function add() {
		if (!empty($this->data)) {
			$this->Tgdongdoanphikhoa->create();
			if ($this->Tgdongdoanphikhoa->save($this->data)) {
				$this->Session->setFlash(__('The tgdongdoanphikhoa has been saved', true));
				$this->redirect(array('action' => 'index'));
			} else {
				$this->Session->setFlash(__('The tgdongdoanphikhoa could not be saved. Please, try again.', true));
			}
		}
		$chidoansvs = $this->Tgdongdoanphikhoa->Chidoansv->find('list');
		$thangs = $this->Tgdongdoanphikhoa->Thang->find('list');
		$namhocs = $this->Tgdongdoanphikhoa->Namhoc->find('list');
		$khoas = $this->Tgdongdoanphikhoa->Khoa->find('list');
		$this->set(compact('chidoansvs', 'thangs', 'namhocs', 'khoas'));
	}

	function edit($id = null) {
		if (!$id && empty($this->data)) {
			$this->Session->setFlash(__('Invalid tgdongdoanphikhoa', true));
			$this->redirect(array('action' => 'index'));
		}
		if (!empty($this->data)) {
			if ($this->Tgdongdoanphikhoa->save($this->data)) {
				$this->Session->setFlash(__('The tgdongdoanphikhoa has been saved', true));
				$this->redirect(array('action' => 'index'));
			} else {
				$this->Session->setFlash(__('The tgdongdoanphikhoa could not be saved. Please, try again.', true));
			}
		}
		if (empty($this->data)) {
			$this->data = $this->Tgdongdoanphikhoa->read(null, $id);
		}
		$chidoansvs = $this->Tgdongdoanphikhoa->Chidoansv->find('list');
		$thangs = $this->Tgdongdoanphikhoa->Thang->find('list');
		$namhocs = $this->Tgdongdoanphikhoa->Namhoc->find('list');
		$khoas = $this->Tgdongdoanphikhoa->Khoa->find('list');
		$this->set(compact('chidoansvs', 'thangs', 'namhocs', 'khoas'));
	}

	function delete($id = null) {
		if (!$id) {
			$this->Session->setFlash(__('Invalid id for tgdongdoanphikhoa', true));
			$this->redirect(array('action'=>'index'));
		}
		if ($this->Tgdongdoanphikhoa->delete($id)) {
			$this->Session->setFlash(__('Tgdongdoanphikhoa deleted', true));
			$this->redirect(array('action'=>'index'));
		}
		$this->Session->setFlash(__('Tgdongdoanphikhoa was not deleted', true));
		$this->redirect(array('action' => 'index'));
	}

	function doanp($khoa_id=null,$namhoc=null)
	{
		if ($khoa_id==null || $namhoc==null)
		{
			$this->redirect(array('controller'=>'pages','action'=>'home'));
		}
		
		// can luu: sinhvien_id, thang_id, ngaydong, ghichu
        if(!empty($this->data['Tgdongdoanphikhoa']['dadong']))
        {           
        		$error = 0;
                foreach($this->data['Tgdongdoanphikhoa']['dadong'] as $value)
                {
                	
                	$arrcd =split('_', $value);
                    $record['Tgdongdoanphikhoa']['thang_id']=$arrcd[0];
                    $record['Tgdongdoanphikhoa']['chidoansv_id']=$arrcd[1];
                    $record['Tgdongdoanphikhoa']['ngaydong']=date('Y-m-d');
                    $record['Tgdongdoanphikhoa']['namhoc_id']=$namhoc;
                    $record['Tgdongdoanphikhoa']['khoa_id']=$khoa_id;
                    $record['Tgdongdoanphikhoa']['sotien']=$this->data['Tgdongdoanphikhoa']['chidoan'][$arrcd[1]]*$this->data['Tgdongdoanphikhoa']['doanphithang'][$arrcd[0]];
                    
                    if(!$this->Tgdongdoanphikhoa->saveAll($record))
                    {
                    	$error = 1;
                   	    $this->Session->setFlash(__('Thông tin không thể lưu', true),'error');
                        break;
                    }
                }
                
                 if($error!=1)
                	$this->Session->setFlash(__('Bạn đã cập nhật Đoàn phí thành công!!', true),'success');
                $this->redirect(array('action'=>'doanp',$khoa_id,$namhoc));    
        }
        //lay thong tin chi doan hien dang cap nhat doan phi
        $khoa=$this->Khoa->find('first',array(
                'conditions'=>array('Khoa.id'=>$khoa_id),
                'recursive'=>-1));
        /** ***********************************
         * Lay data cot dong
         *************************************** */
        //tim cac Chi doan thuoc khoa (lay id cua khoa)
		$querystring= "select Tgiachidoan.chidoansv_id, sl as soluong
    					from tgiachidoans as Tgiachidoan, (select a.chidoansv_id, count(sinhvien_id) as sl from tgiachidoans a where ngaychuyen is NULL group by a.chidoansv_id) as Tgiachidoan1
    					where Tgiachidoan.chidoansv_id = Tgiachidoan1.chidoansv_id and Tgiachidoan1.sl>0 and ngaychuyen is NULL
    						group by Tgiachidoan.chidoansv_id";
    	$laycd_act = $this->Tgdongdoanphikhoa->query($querystring);
    	
    	$cd_id = array();
    	$soluong = array();
    	foreach ($laycd_act as $cd) 
    	{
    		$cd_id[] = $cd['Tgiachidoan']['chidoansv_id'];
    		$soluong[$cd['Tgiachidoan']['chidoansv_id']] =  $cd['Tgiachidoan1']['soluong'];
    	}    
		//hien thi sinhvien can cu vao $sv 
		$this->Chidoansv->displayField='tenchidoan' ;
    	$chidoans = $this->Chidoansv->find('list',array('conditions'=>array('khoa_id'=>$khoa_id,'khoahoc_id !='=>null,'id'=>$cd_id)));
    	
        /*$sinhviens=$this->Sinhvien->find('list',array('conditions'=>array('Sinhvien.id'=>$sv,
                                                        'OR'=>array('ngayvaodoan !='=>NULL,
                                                        'ngayvaodoan !='=>'0000-00-00'),'truongthanhdoan '=>NULL),
                						'fields'=>array('id','name'),'recursive'=>-1));*/
		
        //lay cac thang cua namhoc
        $thangs=$this->Thang->find('list',array('fields'=>array('id','thang'),
                'conditions'=>array('namhoc_id'=>$namhoc)));
        
        /** ************************************
         * cac sinh vien da dong doan phi
         *************************************** */
        if(!empty($thangs))
        	$thang_id = array_keys($thangs);
        else
        	$thang_id= array();
        $doanphi = $this->Doanphisv->find('list',array(
        					'fields'=>array('thang_id','sotien'),
        					'conditions'=>array('thang_id'=>$thang_id)));
        //cot "name" dc dinh  nghia trong Model cua Tgdongdoanphisv, lay du lieu de kiem tra xem sinhvien do
        //da dong vao thang nao
        $dulieu=$this->Tgdongdoanphikhoa->find('list',array(
                'conditions'=>array('namhoc_id'=>$namhoc,'khoa_id'=>$khoa_id),
                'fields'=>array('name','ngaydong'),
                'recursive'=>-1));
        $dadongs = $this->Tgdongdoanphikhoa->find('all',array(
        					'fields'=>array('Tgdongdoanphikhoa.thang_id','sum(Tgdongdoanphikhoa.sotien) as sotien'),
        					'conditions'=>array('Tgdongdoanphikhoa.namhoc_id'=>$namhoc,
        										'Tgdongdoanphikhoa.khoa_id'=>$khoa_id,
        										'Tgdongdoanphikhoa.chidoansv_id'=>array_keys($chidoans)),
        					'group'=>array('thang_id')));
        
        
        $dathu = array();
        foreach ($dadongs as $dadong)
        {
        	$dathu[$dadong['Tgdongdoanphikhoa']['thang_id']] =$dadong[0]['sotien']; 
        }
        
        $nhoc = $this->Namhoc->find('list',array('conditions'=>array('id'=>$namhoc),
        									'fields'=>array('id','namhoc')));
       $khoahocs = $this->Khoahoc->find('list',array('fields'=>array('nambatdau')));
        $this->set(compact('khoahocs','khoa','chidoans','thangs','khoa_id','namhoc','dulieu','nhoc','dathu','chidoan','doanphi','soluong'));
        
	}

	function xuatexcel($khoa_id=null,$namhoc=null,$chon=0,$khoahoc=0)
	{
		$this->layout = '';
		if($chon==0)
			$querystring= "select Tgiachidoan.chidoansv_id, sl as soluong
    					from tgiachidoans as Tgiachidoan, (select a.chidoansv_id, count(sinhvien_id) as sl from tgiachidoans a where ngaychuyen is NULL group by a.chidoansv_id) as Tgiachidoan1
    					where Tgiachidoan.chidoansv_id = Tgiachidoan1.chidoansv_id and Tgiachidoan1.sl>0 and ngaychuyen is NULL
    						group by Tgiachidoan.chidoansv_id";
		else
			$querystring= "select Tgiachidoancb.chidoansv_id, sl as soluong
    					from tgiachidoancbs as Tgiachidoancb, (select a.chidoansv_id, count(canbo_id) as sl from tgiachidoancbs a where ngaychuyen is NULL group by a.chidoansv_id) as Tgiachidoancb1
    					where Tgiachidoancb.chidoansv_id = Tgiachidoancb1.chidoansv_id and Tgiachidoancb1.sl>0 and ngaychuyen is NULL
    						group by Tgiachidoancb.chidoansv_id";
		
    	$laycd_act = $this->Tgdongdoanphikhoa->query($querystring);
    	
    	$cd_id = array();
    	$soluong = array();
    	if($chon==0)
    	{
    		foreach ($laycd_act as $cd) 
    		{
    			$cd_id[] = $cd['Tgiachidoan']['chidoansv_id'];
    			$soluong[$cd['Tgiachidoan']['chidoansv_id']] =  $cd['Tgiachidoan1']['soluong'];
    		}
    	}
    	else
    	{
    		foreach ($laycd_act as $cd) 
    		{
    			$cd_id[] = $cd['Tgiachidoancb']['chidoansv_id'];
    			$soluong[$cd['Tgiachidoancb']['chidoansv_id']] =  $cd['Tgiachidoancb1']['soluong'];
    		}
    	}

    	 //lay cac thang cua namhoc
        $thangs=$this->Thang->find('list',array('fields'=>array('id','thang'),
                'conditions'=>array('namhoc_id'=>$namhoc)));
        
        if(!empty($thangs))
        	$thang_id = array_keys($thangs);
        else
        	$thang_id= array();
    	
    	$this->Chidoansv->displayField='tenchidoan' ;
		if($chon==0)
		{	
			if(empty($khoahoc))
			{
				$khoahoc = 0;
				$conditions = array('khoa_id'=>$khoa_id,'khoahoc_id !='=>null,'id'=>$cd_id);
			}
			else
				$conditions = array('khoa_id'=>$khoa_id,'khoahoc_id '=>$khoahoc,'khoahoc_id !='=>null,'id'=>$cd_id);
    		$chidoans = $this->Chidoansv->find('list',array('conditions'=>$conditions));
    		$doanphi = $this->Doanphisv->find('list',array(
        					'fields'=>array('thang_id','sotien'),
        					'conditions'=>array('thang_id'=>$thang_id)));
		}
    	else
    	{
    		$chidoans = $this->Chidoansv->find('list',array('conditions'=>array('khoa_id'=>$khoa_id,'khoahoc_id'=>null,'id'=>$cd_id)));
    		$doanphi = $this->Doanphicb->find('list',array(
        					'fields'=>array('thang_id','sotien'),
        					'conditions'=>array('thang_id'=>$thang_id)));
    	}

       
        
        //cot "name" dc dinh  nghia trong Model cua Tgdongdoanphisv, lay du lieu de kiem tra xem sinhvien do
        //da dong vao thang nao
        $dulieu=$this->Tgdongdoanphikhoa->find('list',array(
                'conditions'=>array('namhoc_id'=>$namhoc,'khoa_id'=>$khoa_id),
                'fields'=>array('name','ngaydong'),
                'recursive'=>-1));
        $dadongs = $this->Tgdongdoanphikhoa->find('all',array(
        					'fields'=>array('Tgdongdoanphikhoa.thang_id','sum(Tgdongdoanphikhoa.sotien) as sotien'),
        					'conditions'=>array('Tgdongdoanphikhoa.namhoc_id'=>$namhoc,
        										'Tgdongdoanphikhoa.khoa_id'=>$khoa_id,
        										'Tgdongdoanphikhoa.chidoansv_id'=>array_keys($chidoans)),
        					'group'=>array('thang_id')));
        
        
        $dathu = array();
        foreach ($dadongs as $dadong)
        {
        	$dathu[$dadong['Tgdongdoanphikhoa']['thang_id']] =$dadong[0]['sotien']; 
        }
        
        $nhoc = $this->Namhoc->find('list',array('conditions'=>array('id'=>$namhoc),
        									'fields'=>array('id','namhoc')));
       $khoa=$this->Khoa->find('first',array(
                'conditions'=>array('Khoa.id'=>$khoa_id),
                'recursive'=>-1));
       $nhoc = $this->Namhoc->find('list',array('conditions'=>array('id'=>$namhoc),
        									'fields'=>array('id','namhoc')));
        $this->set(compact('nhoc','khoa','chidoans','thangs','dulieu','dathu','doanphi','soluong','khoa_id','namhoc','chon','khoahoc'));
	}
	
	function tinhthuchi($phicdsvs = array())
	{
		if(!empty($phicdsvs))
		{
			//thu khac
			$thukhacnam = 0;
			$thukhacthang = 0;
			foreach ($phicdsvs as $phicdsvs)
			{
				//if($phicdsv[''])
			}
		}
	}

	function xoaddong($ttcd=null, $ttkhoa = null)
	{
		if(!$ttcd || !$ttkhoa)
			$this->redirect(array('controller'=>'pages','action'=>'home'));
		$arrsv =split('_', $ttcd);
		$arrcd =split('_', $ttkhoa);
		$thg_id=$arrsv[0];
		$cd_id = $arrsv[1];
		$this->Tgdongdoanphikhoa->query('delete from tgdongdoanphikhoas where chidoansv_id='.$cd_id .' and thang_id='.$thg_id);
		$cd = $arrcd[0];
		$nh = $arrcd[1];
		$this->redirect(array('action'=>'doanp',$cd,$nh));
	}
	
	function updatechidoan($khoa_id=null,$namhoc=null)
	{
		$this->layout = 'ajax';
		$chon = $this->data['Tgdongdoanphikhoa']['chon'];
		$khoahoc = $this->data['Tgdongdoanphikhoa']['khoahoc'];
		if($chon==0)
			$querystring= "select Tgiachidoan.chidoansv_id, sl as soluong
    					from tgiachidoans as Tgiachidoan, (select a.chidoansv_id, count(sinhvien_id) as sl from tgiachidoans a where ngaychuyen is NULL group by a.chidoansv_id) as Tgiachidoan1
    					where Tgiachidoan.chidoansv_id = Tgiachidoan1.chidoansv_id and Tgiachidoan1.sl>0 and ngaychuyen is NULL
    						group by Tgiachidoan.chidoansv_id";
		else
			$querystring= "select Tgiachidoancb.chidoansv_id, sl as soluong
    					from tgiachidoancbs as Tgiachidoancb, (select a.chidoansv_id, count(canbo_id) as sl from tgiachidoancbs a where ngaychuyen is NULL group by a.chidoansv_id) as Tgiachidoancb1
    					where Tgiachidoancb.chidoansv_id = Tgiachidoancb1.chidoansv_id and Tgiachidoancb1.sl>0 and ngaychuyen is NULL
    						group by Tgiachidoancb.chidoansv_id";
		
    	$laycd_act = $this->Tgdongdoanphikhoa->query($querystring);
    	
    	$cd_id = array();
    	$soluong = array();
    	if($chon==0)
    	{
    		foreach ($laycd_act as $cd) 
    		{
    			$cd_id[] = $cd['Tgiachidoan']['chidoansv_id'];
    			$soluong[$cd['Tgiachidoan']['chidoansv_id']] =  $cd['Tgiachidoan1']['soluong'];
    		}
    	}
    	else
    	{
    		foreach ($laycd_act as $cd) 
    		{
    			$cd_id[] = $cd['Tgiachidoancb']['chidoansv_id'];
    			$soluong[$cd['Tgiachidoancb']['chidoansv_id']] =  $cd['Tgiachidoancb1']['soluong'];
    		}
    	}

    	 //lay cac thang cua namhoc
        $thangs=$this->Thang->find('list',array('fields'=>array('id','thang'),
                'conditions'=>array('namhoc_id'=>$namhoc)));
        
        if(!empty($thangs))
        	$thang_id = array_keys($thangs);
        else
        	$thang_id= array();
    	
    	$this->Chidoansv->displayField='tenchidoan' ;
		if($chon==0)
		{	
			if(empty($khoahoc))
			{
				$khoahoc = 0;
				$conditions = array('khoa_id'=>$khoa_id,'khoahoc_id !='=>null,'id'=>$cd_id);
			}
			else
				$conditions = array('khoa_id'=>$khoa_id,'khoahoc_id '=>$khoahoc,'khoahoc_id !='=>null,'id'=>$cd_id);
    		$chidoans = $this->Chidoansv->find('list',array('conditions'=>$conditions));
    		$doanphi = $this->Doanphisv->find('list',array(
        					'fields'=>array('thang_id','sotien'),
        					'conditions'=>array('thang_id'=>$thang_id)));
		}
    	else
    	{
    		$khoahoc = 0;
    		$chidoans = $this->Chidoansv->find('list',array('conditions'=>array('khoa_id'=>$khoa_id,'khoahoc_id'=>null,'id'=>$cd_id)));
    		$doanphi = $this->Doanphicb->find('list',array(
        					'fields'=>array('thang_id','sotien'),
        					'conditions'=>array('thang_id'=>$thang_id)));
    	}

       
        
        //cot "name" dc dinh  nghia trong Model cua Tgdongdoanphisv, lay du lieu de kiem tra xem sinhvien do
        //da dong vao thang nao
        $dulieu=$this->Tgdongdoanphikhoa->find('list',array(
                'conditions'=>array('namhoc_id'=>$namhoc,'khoa_id'=>$khoa_id),
                'fields'=>array('name','ngaydong'),
                'recursive'=>-1));
        $dadongs = $this->Tgdongdoanphikhoa->find('all',array(
        					'fields'=>array('Tgdongdoanphikhoa.thang_id','sum(Tgdongdoanphikhoa.sotien) as sotien'),
        					'conditions'=>array('Tgdongdoanphikhoa.namhoc_id'=>$namhoc,
        										'Tgdongdoanphikhoa.khoa_id'=>$khoa_id,
        										'Tgdongdoanphikhoa.chidoansv_id'=>array_keys($chidoans)),
        					'group'=>array('thang_id')));
        
        
        $dathu = array();
        foreach ($dadongs as $dadong)
        {
        	$dathu[$dadong['Tgdongdoanphikhoa']['thang_id']] =$dadong[0]['sotien']; 
        }
        
        $nhoc = $this->Namhoc->find('list',array('conditions'=>array('id'=>$namhoc),
        									'fields'=>array('id','namhoc')));
       
        $this->set(compact('chidoans','thangs','dulieu','dathu','doanphi','soluong','khoa_id','namhoc','chon','khoahoc'));
	}
	
}
